CREATE DATABASE Cms
GO
USE Cms
GO

/* table */
CREATE TABLE staffs
(
	STID INT IDENTITY(1,1) PRIMARY KEY,
	STname NVARCHAR(50) NOT NULL,
	STpass VARCHAR(20) NOT NULL,
)
GO
INSERT INTO staffs
(
	STname,
	STpass
	
)
VALUES
(
	'admin',
	'admin'
)
GO
CREATE TABLE techchers
(
	TID INT IDENTITY(1,1) PRIMARY KEY,
	Tname NVARCHAR(50) NOT NULL,
	Tpass VARCHAR(20) NOT NULL,
	TFullname NVARCHAR(100) NOT NULL,
	TDob DATETIME NOT NULL,
)
GO
CREATE TABLE courses
(
	CID INT IDENTITY(1,1) PRIMARY KEY,
	CName NVARCHAR(50) NOT NULL,
	TID INT FOREIGN KEY REFERENCES techchers(TID),
	CBegintime DATETIME NOT NULL,
	CEndtime DATETIME NOT NULL,	
)
GO
CREATE TABLE subjects
(
	SID INT IDENTITY(1,1) PRIMARY KEY,
	SROLES INT,
	SName NVARCHAR(50) NOT NULL,
)
GO
CREATE TABLE openupload
(
	UPID INT IDENTITY(1,1) PRIMARY KEY,
	SID INT FOREIGN KEY REFERENCES subjects(SID),
	UPName NVARCHAR(50),
	UPStarttime DATETIME NOT NULL,
	UPExpri DATETIME NOT NULL,
)
GO
CREATE TABLE documents
(
	SDID INT IDENTITY(1,1) PRIMARY KEY,
	UPID INT FOREIGN KEY REFERENCES openupload(UPID),
	DROLES INT,
	Dname NVARCHAR(50),
	DFilename NVARCHAR(50)
)
GO
CREATE TABLE class
(
	CLID INT IDENTITY(1,1) PRIMARY KEY,
	CID INT FOREIGN KEY REFERENCES courses(CID),
	SID INT FOREIGN KEY REFERENCES subjects(SID),
	CLName NVARCHAR(50) NOT NULL,
	CLCreatetime DATETIME NOT NULL,
)
GO
CREATE TABLE students
(
	STDID INT IDENTITY(1,1) PRIMARY KEY,
	CLID INT FOREIGN KEY REFERENCES class(CLID),
	STDname NVARCHAR(50) NOT NULL,
	STDpass VARCHAR(20) NOT NULL,
	STDFullname NVARCHAR(100) NOT NULL,
	STDDob DATETIME NOT NULL,
	STDRollnumber VARCHAR(10) NOT NULL,
)
